package com.imist.jdbc.dao;

import com.imist.jdbc.bean.Message;
import com.imist.jdbc.common.ConnectionUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MessageDao {


    public List<Message> getMessages(int page ,int pageSize){
        Connection connection = ConnectionUtil.getConnection();

        String sql = "select * from message order by create_time desc limit ?,?";//limit m,n从m条开始取出n条记录
        List<Message> messages = new ArrayList<>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1,(page -1) * pageSize);
            statement.setInt(2,pageSize);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                messages.add(new Message(resultSet.getLong("id"),
                        resultSet.getLong("user_id"),
                        resultSet.getString("username"),
                        resultSet.getString("title"),
                        resultSet.getString("content"),
                        resultSet.getTimestamp("create_time")
                        ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnectionUtil.release(connection,statement,resultSet);
        }
        return messages;
    }

    /**
     * 计算所有留言数量
     * @return
     */
    public int countMessages(){
        Connection connection = ConnectionUtil.getConnection();
        String sql = "select count(*) total from message ";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                return resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnectionUtil.release(connection,statement,resultSet);
        }
        return 0;
    }

    public boolean save(Message message) {
        Connection conn = ConnectionUtil.getConnection();
        String sql = "INSERT message(user_id, username, title, content, create_time) VALUES   (?,?,?,?,?)";
        PreparedStatement statement = null;
        try {
            statement= conn.prepareStatement(sql);
            statement.setLong(1,message.getUserId());
            statement.setString(2,message.getUsername());
            statement.setString(3,message.getTitle());
            statement.setString(4,message.getContent());
            statement.setTimestamp(5,new Timestamp(message.getCreateTime().getTime()));
            int i = statement.executeUpdate();
            if (i > 0){
                return true;
            }else {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("保存留言失败");
            return false;
        }finally {
            ConnectionUtil.release(conn,statement);
        }
    }
}
